"""
Author : 张程东
File : check_biye.py 
"""
import pandas as pd
import os

# 总表F:\图书馆\数据\全日制毕业信息对比
# 分表F:\图书馆\数据\全日制毕业信息对比\全日制毕业证书\2004\2004-JX17-0030\2004-0017-0030.xlsx

def deal_space(x):
    """
    去空函数
    :param x:字符串
    :return:去空字符串
    """
    return x.replace(" ", "")


def get_one_year_all_data(path, year):
    """
    获取总数据
    :param path: 文件的路径
    :param year: 读取的年份
    :return:该年份的数据,表中是否含有班级信息
    """
    year = year + "年"
    year_data = pd.read_excel(path, sheet_name=year, dtype=object)
    flag = -1
    if set(['班级', '学号', '姓名']).issubset(year_data.columns):
        data = year_data.loc[:, ['姓名', '学号', '班级']].rename(
            columns={"姓名": "student_name", "学号": "student_id", "班级": "class"})
        data.dropna(axis=0, inplace=True)
        # data['student_name'] = data['student_name'].replace("•", "").replace("·", "")
        data['student_name'] = data['student_name'].apply(deal_space)
        data['student_id'] = data['student_id'].apply(deal_space)
        data['class'] = data['class'].apply(deal_space)
        flag = 1  # 含有班级信息
    else:
        data = year_data.loc[:, ['姓名', '学号']].rename(columns={"姓名": "student_name", "学号": "student_id"})
        data.dropna(axis=0, inplace=True)
        data['student_name'] = data['student_name'].apply(deal_space)
        data['student_id'] = data['student_id'].apply(deal_space)
        flag = 0  # 表中没有班级信息
    data['flag'] = "总表数据"
    return data, flag


def get_part_data(path):
    """
    获得识的数据
    :param path:分表路径
    :return:分表中的总数据
    """
    part_allInfo_data = pd.read_excel(path, dtype=object)
    part_allInfo_data = part_allInfo_data.loc[:, ['学号', '姓名']]
    part_allInfo_data.rename(columns={"学号": "student_id", "姓名": "student_name"}, inplace=True)

    # 去空
    part_allInfo_data.dropna(axis=0, inplace=True)

    # 检测哪一行是否含有学院,并将其删除

    part_allInfo_data.drop(index=part_allInfo_data[part_allInfo_data['student_id'].isin(['学号'])].index.tolist(),
                           inplace=True)
    # 将数据进行去空
    part_allInfo_data['student_id'] = part_allInfo_data['student_id'].astype(str).apply(deal_space)
    # part_allInfo_data['student_name'] = part_allInfo_data['student_name'].replace("•", "").replace("·", "")
    part_allInfo_data['student_name'] = part_allInfo_data['student_name'].apply(deal_space)
    part_allInfo_data['flag'] = "分表数据"
    return part_allInfo_data


def check_data(all_data, part_data):
    """
    见查数据表中的数据
    :param all_data:总表data
    :param part_data:分表data
    :return:不正确的数据
    """
    search_data = all_data.loc[all_data['student_id'].isin(list(part_data['student_id'])), :]
    data = pd.concat([part_data, search_data], axis=0, ignore_index=True)
    data['student_name']=data['student_name'].apply(lambda x:x.replace("·","").replace("•",""))
    data.drop_duplicates(subset=['student_id', 'student_name'], keep=False, inplace=True)
    data.sort_values(by="student_id", inplace=True)
    return data


def mkdir_path(path):
    """
    文件的创建
    :param path:输出路径
    :return:
    """
    if not os.path.exists(path):
        os.mkdir(path)


def save_error_data(data, path):
    """
    保存错误数据
    :param data:错误数据
    :param path:保存路径
    :return:
    """
    mkdir_path(path)
    if not data.empty:
        data.rename(columns={"student_id": "学号", "student_name": "姓名"})
        data.to_excel(path + "error.xlsx", index=None)


if __name__ == "__main__":
    all_data, flag = get_one_year_all_data(r"F:\图书馆\数据\全日制毕业信息对比\05-20毕业生.xlsx", "2008")
    part_data = get_part_data(r"F:\图书馆\数据\全日制毕业信息对比\全日制毕业证书\2008\2008-JX17-0017\2008-JX17-0017.xlsx")
    error_data = check_data(all_data, part_data)
    save_error_data(error_data, r"./graduate_error/")
